DB Log Shrink
-------------
if 1000 < (SELECT size * 8 / 1024 AS [SizeMB]
FROM sys.master_files WHERE database_id = DB_ID('TechnoPower') AND type_desc = 'LOG')--name AS [File Name],
begin
-- Truncate the log by changing the database recovery model to SIMPLE.
--ALTER DATABASE TRINETRA_Pilot
--SET RECOVERY SIMPLE;
--GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (2, 1);  -- here 2 is the file ID for trasaction log file,you can also mention the log file name (dbname_log)
GO
-- Reset the database recovery model.
--ALTER DATABASE TRINETRA_Pilot
--SET RECOVERY FULL;
end
------------------------------------------------------------------------------------------------
Index rebuild
-------------
DECLARE @TableName varchar(255)
DECLARE TableCursor CURSOR 
FOR

SELECT   OBJECT_NAME(ps.OBJECT_ID) AS [Object Name] 
FROM sys.dm_db_index_physical_stats(DB_ID(),NULL, NULL, NULL ,'LIMITED') AS ps
INNER JOIN sys.indexes AS i WITH (NOLOCK)
ON ps.[object_id] = i.[object_id] 
AND ps.index_id = i.index_id
WHERE database_id = DB_ID() AND page_count > 250 and avg_fragmentation_in_percent > 40  OPTION (RECOMPILE);

OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName

WHILE @@FETCH_STATUS = 0
BEGIN
	DBCC DBREINDEX(@TableName,' ',90)
	--ALTER INDEX ALL ON rvlfMsgInHst REBUILD WITH (FILLFACTOR = 80);
	FETCH NEXT FROM TableCursor INTO @TableName
END

CLOSE TableCursor
DEALLOCATE TableCursor
------------------------------------------------------------------------------------
 USE [TechnoPower]
GO
 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

create PROCEDURE [dbo].[rvlf_sp_DeleteData_7310]
AS
/************************************************************
Procedure Name	: rvlf_sp_DeleteData

Description		: Data remove from database
*************************************************************/
BEGIN
SET NOCOUNT ON
	--Maintain two month data from the current utc.
	--Delete the records except boxmsginid reference
	BEGIN TRAN DeleteTransaction_FMS
	BEGIN TRY
		
		DELETE dbo.rvlfMsgInHst_Techno WHERE OriginDateTime < CAST(CONVERT(VARCHAR(10), DATEADD(DD,-7,DATEADD(MONTH,-6,GETUTCDATE())), 110) AS DATETIME)
		AND IsArchived=1 AND MessageInID NOT IN (SELECT MessageInID FROM dbo.rvlfBoxId)
		
		--Frame mail template for deleted records
		DECLARE @rowCount varchar(25)
		SELECT @rowCount = CAST(@@ROWCOUNT AS VARCHAR(25))
		DECLARE @bodytag varchar(max) = '<div style="font-family: Verdana; font-size: 12px;">
		<p><span style="color: rgb(0, 102, 153);"><strong>Dear All,</strong></span></p>
		<p><span style="color: rgb(0, 102, 153);"><u><em><strong>6 month Old data are removed successfully from database. 
		Only 6 month data available from this date ('+ CAST(CAST(CONVERT(VARCHAR(10), DATEADD(DD,-7,DATEADD(MONTH,-2,GETUTCDATE())), 110) AS DATETIME) 
		AS VARCHAR) +')'+'.</strong></em></u></span></p>
		<table border="0" cellpadding="1" cellspacing="1" style="width: 439px; color: rgb(0, 102, 153);font-family: Verdana;">
		<tbody>
		<tr>
		<td align="left" nowrap="nowrap" style="font-family: Verdana; font-size: 12px;" valign="top"><strong>Details</strong></td>
		<td>&nbsp;</td>
		<td>&nbsp;</td>
		</tr>
		<tr style="font-family: Verdana; font-size: 12px;">
		<td align="left" valign="top">
		<strong>Source Database Name</strong>
		</td>
		<td align="left" valign="top">
		:
		</td>
		<td>
		TRINETRA_Pilot
		</td>
		</tr>
		<tr style="font-family: Verdana; font-size: 12px;">
		<td align="left" valign="top">
		<strong>No of deleted records</strong>
		</td>
		<td align="left" valign="top">
		:
		</td>
		<td>
		'+@rowCount+' 
		</td>
		</tr>
		</tbody>
		</table>
		<hr />
		<p>
		<span style="color: rgb(0, 102, 153);"><b>Best Regards,<br />
		</b></span><a href="http://www.trinetrawireless.com">
		<img alt="trinetrawireless" src=""
		style="border: 0px none; width: 94px; height: 34px;" /></a></p>
		<p>
		<span style="font-size: 10px;"><span style="color: rgb(0, 102, 153);">This is a system generated mail</span><br />
		</span>
		</p>
		<p>
		<b><b>&nbsp;</b></b></p>
		</div>'
		
		--Triggering mail once the delete process completed
	--	EXEC msdb.dbo.sp_send_dbmail @profile_name='TRINETRA',
	--	@recipients='',
	--	@copy_recipients = '',
	--	@subject='TRINETRA | Reg : Data removed - FMS',@body= @bodytag,@body_format = 'HTML'
		
		COMMIT TRAN
	END TRY
	BEGIN CATCH
		
		ROLLBACK TRAN
		DECLARE @ErrMsg varchar(500)
		SET @ErrMsg = 'An Error occured during delete of the packet message : ' + ERROR_MESSAGE() 
	--	EXEC msdb.dbo.sp_send_dbmail @profile_name='TRINETRA',
	--	@recipients='',
	--	@copy_recipients = '',
	--	@subject='TRINETRA | Reg : Data removed - FMS | ERROR',@body= @ErrMsg,@body_format = 'HTML'
		
	END CATCH
	
	SET NOCOUNT OFF
END

------------------------------------------------------------------
USE [TechnoPower]
GO
 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

create PROCEDURE [dbo].[rvlf_sp_DeleteData_355]
AS
/************************************************************
Procedure Name	: rvlf_sp_DeleteData

Description		: Data remove from database
*************************************************************/
BEGIN
SET NOCOUNT ON
	--Maintain two month data from the current utc.
	--Delete the records except boxmsginid reference
	BEGIN TRAN DeleteTransaction_FMS
	BEGIN TRY
		
		DELETE dbo.rvlfMsgInHst_Techno_355 WHERE OriginDateTime < CAST(CONVERT(VARCHAR(10), DATEADD(DD,-7,DATEADD(MONTH,-6,GETUTCDATE())), 110) AS DATETIME)
		AND IsArchived=1 AND MessageInID NOT IN (SELECT MessageInID FROM dbo.rvlfBoxId)
		
		--Frame mail template for deleted records
		DECLARE @rowCount varchar(25)
		SELECT @rowCount = CAST(@@ROWCOUNT AS VARCHAR(25))
		DECLARE @bodytag varchar(max) = '<div style="font-family: Verdana; font-size: 12px;">
		<p><span style="color: rgb(0, 102, 153);"><strong>Dear All,</strong></span></p>
		<p><span style="color: rgb(0, 102, 153);"><u><em><strong>6 month Old data are removed successfully from database. 
		Only 6 month data available from this date ('+ CAST(CAST(CONVERT(VARCHAR(10), DATEADD(DD,-7,DATEADD(MONTH,-2,GETUTCDATE())), 110) AS DATETIME) 
		AS VARCHAR) +')'+'.</strong></em></u></span></p>
		<table border="0" cellpadding="1" cellspacing="1" style="width: 439px; color: rgb(0, 102, 153);font-family: Verdana;">
		<tbody>
		<tr>
		<td align="left" nowrap="nowrap" style="font-family: Verdana; font-size: 12px;" valign="top"><strong>Details</strong></td>
		<td>&nbsp;</td>
		<td>&nbsp;</td>
		</tr>
		<tr style="font-family: Verdana; font-size: 12px;">
		<td align="left" valign="top">
		<strong>Source Database Name</strong>
		</td>
		<td align="left" valign="top">
		:
		</td>
		<td>
		TRINETRA_Pilot
		</td>
		</tr>
		<tr style="font-family: Verdana; font-size: 12px;">
		<td align="left" valign="top">
		<strong>No of deleted records</strong>
		</td>
		<td align="left" valign="top">
		:
		</td>
		<td>
		'+@rowCount+' 
		</td>
		</tr>
		</tbody>
		</table>
		<hr />
		<p>
		<span style="color: rgb(0, 102, 153);"><b>Best Regards,<br />
		</b></span><a href="http://www.trinetrawireless.com">
		<img alt="trinetrawireless" src=""
		style="border: 0px none; width: 94px; height: 34px;" /></a></p>
		<p>
		<span style="font-size: 10px;"><span style="color: rgb(0, 102, 153);">This is a system generated mail</span><br />
		</span>
		</p>
		<p>
		<b><b>&nbsp;</b></b></p>
		</div>'
		
		--Triggering mail once the delete process completed
	--	EXEC msdb.dbo.sp_send_dbmail @profile_name='TRINETRA',
	--	@recipients='',
	--	@copy_recipients = '',
	--	@subject='TRINETRA | Reg : Data removed - FMS',@body= @bodytag,@body_format = 'HTML'
		
		COMMIT TRAN
	END TRY
	BEGIN CATCH
		
		ROLLBACK TRAN
		DECLARE @ErrMsg varchar(500)
		SET @ErrMsg = 'An Error occured during delete of the packet message : ' + ERROR_MESSAGE() 
	--	EXEC msdb.dbo.sp_send_dbmail @profile_name='TRINETRA',
	--	@recipients='',
	--	@copy_recipients = '',
	--	@subject='TRINETRA | Reg : Data removed - FMS | ERROR',@body= @ErrMsg,@body_format = 'HTML'
		
	END CATCH
	
	SET NOCOUNT OFF
END

--------------------------------------------------
USE [TechnoPower]
GO
 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

create PROCEDURE [dbo].[rvlf_sp_DeleteData_alarms]
AS
/************************************************************
Procedure Name	: rvlf_sp_DeleteData

Description		: Data remove from database
*************************************************************/
BEGIN
SET NOCOUNT ON
	--Maintain two month data from the current utc.
	--Delete the records except boxmsginid reference
	BEGIN TRAN DeleteTransaction_FMS
	BEGIN TRY
		
		DELETE dbo.rvlfAlarm WHERE DateTimeCreated < CAST(CONVERT(VARCHAR(10), DATEADD(DD,-7,DATEADD(MONTH,-6,GETUTCDATE())), 110) AS DATETIME)
		AND IsArchived=1 
		
		--Frame mail template for deleted records
		DECLARE @rowCount varchar(25)
		SELECT @rowCount = CAST(@@ROWCOUNT AS VARCHAR(25))
		DECLARE @bodytag varchar(max) = '<div style="font-family: Verdana; font-size: 12px;">
		<p><span style="color: rgb(0, 102, 153);"><strong>Dear All,</strong></span></p>
		<p><span style="color: rgb(0, 102, 153);"><u><em><strong>6 month Old data are removed successfully from database. 
		Only 6 month data available from this date ('+ CAST(CAST(CONVERT(VARCHAR(10), DATEADD(DD,-7,DATEADD(MONTH,-2,GETUTCDATE())), 110) AS DATETIME) 
		AS VARCHAR) +')'+'.</strong></em></u></span></p>
		<table border="0" cellpadding="1" cellspacing="1" style="width: 439px; color: rgb(0, 102, 153);font-family: Verdana;">
		<tbody>
		<tr>
		<td align="left" nowrap="nowrap" style="font-family: Verdana; font-size: 12px;" valign="top"><strong>Details</strong></td>
		<td>&nbsp;</td>
		<td>&nbsp;</td>
		</tr>
		<tr style="font-family: Verdana; font-size: 12px;">
		<td align="left" valign="top">
		<strong>Source Database Name</strong>
		</td>
		<td align="left" valign="top">
		:
		</td>
		<td>
		TRINETRA_Pilot
		</td>
		</tr>
		<tr style="font-family: Verdana; font-size: 12px;">
		<td align="left" valign="top">
		<strong>No of deleted records</strong>
		</td>
		<td align="left" valign="top">
		:
		</td>
		<td>
		'+@rowCount+' 
		</td>
		</tr>
		</tbody>
		</table>
		<hr />
		<p>
		<span style="color: rgb(0, 102, 153);"><b>Best Regards,<br />
		</b></span><a href="http://www.trinetrawireless.com">
		<img alt="trinetrawireless" src=""
		style="border: 0px none; width: 94px; height: 34px;" /></a></p>
		<p>
		<span style="font-size: 10px;"><span style="color: rgb(0, 102, 153);">This is a system generated mail</span><br />
		</span>
		</p>
		<p>
		<b><b>&nbsp;</b></b></p>
		</div>'
		
		--Triggering mail once the delete process completed
	--	EXEC msdb.dbo.sp_send_dbmail @profile_name='TRINETRA',
	--	@recipients='',
	--	@copy_recipients = '',
	--	@subject='TRINETRA | Reg : Data removed - FMS',@body= @bodytag,@body_format = 'HTML'
		
		COMMIT TRAN
	END TRY
	BEGIN CATCH
		
		ROLLBACK TRAN
		DECLARE @ErrMsg varchar(500)
		SET @ErrMsg = 'An Error occured during delete of the packet message : ' + ERROR_MESSAGE() 
	--	EXEC msdb.dbo.sp_send_dbmail @profile_name='TRINETRA',
	--	@recipients='',
	--	@copy_recipients = '',
	--	@subject='TRINETRA | Reg : Data removed - FMS | ERROR',@body= @ErrMsg,@body_format = 'HTML'
		
	END CATCH
	
	SET NOCOUNT OFF
END

----------------------------------------------
USE [TechnoPower]
GO
 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

create PROCEDURE [dbo].[rvlf_sp_DeleteData_alarmsTRans]
AS
/************************************************************
Procedure Name	: rvlf_sp_DeleteData

Description		: Data remove from database
*************************************************************/
BEGIN
SET NOCOUNT ON
	--Maintain two month data from the current utc.
	--Delete the records except boxmsginid reference
	BEGIN TRAN DeleteTransaction_FMS
	BEGIN TRY
		
		DELETE dbo.rvlfAlarmTransaction WHERE DateTimeCreated < CAST(CONVERT(VARCHAR(10), 
		DATEADD(MONTH,-3,GETUTCDATE()), 110) AS DATETIME)
		
		 
		
		COMMIT TRAN
	END TRY
	BEGIN CATCH
		
		ROLLBACK TRAN
		 
		
	END CATCH
	
	SET NOCOUNT OFF
END


